﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Baimasoft.Manage.SqlServerDAL;
using System.Data;
using HsTec.Mobile.Green.Util;
using System.Text;
using HsTec.Mobile.Green.BLL;

public partial class ReportStorage_ExportReportMonthly : System.Web.UI.Page
{
    Param MyP = new Param();
    TemperatureBLL bll = new TemperatureBLL();
    Dictionary<string, string> dic = new Dictionary<string, string>();
    public string sArea = String.Empty;
    public string sYear = String.Empty;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            this.BindsltArea();
        }
    }

    private void BindsltArea()
    {
        sltArea.DataTextField = "Name";
        sltArea.DataValueField = "ID";
        sltArea.DataSource = bll.GetAreaList();
        sltArea.DataBind();
        sltArea.Items.Insert(0, "全部");
        sltArea.Items[0].Value = "";
    }
    protected void btnSearch_Click(object sender, EventArgs e)
    {
        string sStartYears = sltStartYear.Value.Trim() + sltStartMonth.Value;
        string sEndYears = sltEndYear.Value.Trim() + sltEndMonth.Value;
        GetExportList(sStartYears, sEndYears, 1);
        hidState.Value = "1";
        sArea = sltArea.SelectedItem.Text;
        sYear = sStartYears + "至" + sEndYears;
    }
    private void GetExportList(string sStartYears, string sEndYears, int iType)
    {
        String sTD = String.Empty;
        //1.获取需要的数据
        DataView mydv = new DataView();
        string mysql = " SELECT RTRIM(b.name) AS colname, CASE WHEN h.id IS NOT NULL " +
                           "       THEN 'PK' ELSE '' END AS primarykey, type_name(b.xusertype) " +
                           "       + CASE WHEN b.colstat & 1 = 1 THEN '[ID(' + CONVERT(varchar, ident_seed(a.name)) " +
                           "       + ',' + CONVERT(varchar, ident_incr(a.name)) + ')]' ELSE '' END AS type, b.length, " +
                           "       CASE b.isnullable WHEN 0 THEN 'N' ELSE 'Y' END AS isnull, ISNULL(e.text, '') " +
                           "       AS [default], ISNULL(c.[value], '') AS descript " +
                           " FROM (SELECT g.id, g.colid " +
                           "         FROM sysindexes f, sysindexkeys g " +
                           "         WHERE f.id = g.id AND f.indid = g.indid AND f.indid > 0 AND f.indid < 255 AND " +
                           "               (f.status & 2048) <> 0) h RIGHT OUTER JOIN " +
                           "       syscolumns b INNER JOIN " +
                           "       sysobjects a ON b.id = a.id LEFT OUTER JOIN " +
                           "       sys.extended_properties c ON b.id = c.major_id AND b.colid = c.minor_id LEFT OUTER JOIN " +
                           "       syscomments e ON b.cdefault = e.id ON h.id = b.id AND h.colid = b.colid " +
                           " WHERE (a.id = OBJECT_ID('tb_ReportAnalysis')) and b.name <>'P_ID'" +
                           " ORDER BY b.colid";
        DataView dvField = SQLHelper.ExecuteDataView(mysql);
        string sField = String.Empty;
        for (int i = 0; i < dvField.Count; i++)
        {
            if (dvField[i]["type"].ToString() != "varchar")
            {
                if (dvField[i]["colname"].ToString() != "RA_ID" &&
                    dvField[i]["colname"].ToString() != "Area_ID" &&
                    dvField[i]["colname"].ToString() != "Years" &&
                    dvField[i]["colname"].ToString() != "AddDate" &&
                    dvField[i]["colname"].ToString() != "OperatUser" &&
                    dvField[i]["colname"].ToString() != "OperatDate" &&
                    dvField[i]["colname"].ToString() != "Remark"
                    )
                {
                    sField += sField == "" ? "sum(" + dvField[i]["colname"] + ") as " + dvField[i]["colname"] : ",sum(" + dvField[i]["colname"] + ") as " + dvField[i]["colname"];
                }
            }
            else
            {
                sField += sField == "" ? "max(" + dvField[i]["colname"] + ") as " + dvField[i]["colname"] : ",max(" + dvField[i]["colname"] + ") as " + dvField[i]["colname"];
            }
        }
        if (sltArea.SelectedValue == "")
        {
            mydv = SQLHelper.ExecuteDataView("select " + sField + " from tb_ReportAnalysis where years>='" + sStartYears + "' and years<='" + sEndYears + "' and Area_ID!='41'");
        }
        else
        {
            mydv = SQLHelper.ExecuteDataView("select " + sField + " from tb_ReportAnalysis where years>='" + sStartYears + "' and years<='" + sEndYears + "' and Area_ID='" + sltArea.SelectedValue + "' and Area_ID!='41'");
        }
        //2.填充数值
        if (mydv.Count == 1)
        {
            DataTable dt = mydv.Table;
            for (int i = 0, j = 1; i < dt.Columns.Count - 1; i++, j++)
            {
                sTD = "<td>" + mydv[0][i] + "</td>";
                Label lbl = (Label)this.FindControl("Label" + j);
                lbl.Text = sTD;
            }
        }

    }
}
